<?php
//模型
class cashiersModel extends MY_Model {
	function __construct(){
		parent::__construct();
		$this->table = 'cashiers';
		
		$this->totals = array();
	}
	
	function get_list($arr=array(),$page=1,$size=10){
		
		$result = array('list'=>array(),'count'=>0);
		
		
		
		$where = $this->get_wheres($arr);
		
		
		//$sql = "select * from ".$this->db->dbprefix.$this->table." where {$where} ORDER BY DATE(date) DESC , certificate DESC limit ".($page-1)*$size.",{$size}";
		$sql = "select * from ".$this->db->dbprefix.$this->table." where {$where} ORDER BY DATE(date) DESC limit ".($page-1)*$size.",{$size}";
		
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		$result['list'] = $list;
		
		
		$sql = "select count(*) as num from ".$this->db->dbprefix.$this->table." where {$where} ";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		$count = $row?$row['num']:0;
		
		$result['count'] = $count;
	
		return $result;
	}

	//借支表那里的读取借出的数据
	function get_jiezhi_list($arr=array(),$page=1,$size=10){
		
		$result = array('list'=>array(),'count'=>0);
		
		
		
		$where = $this->get_wheres($arr);
		
		
		$sql = "select * from ".$this->db->dbprefix.$this->table." where {$where} and pid = 0 order by id desc limit ".($page-1)*$size.",{$size}";
		
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		$result['list'] = $list;
		
		
		$sql = "select count(*) as num from ".$this->db->dbprefix.$this->table." where {$where} and pid = 0";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		$count = $row?$row['num']:0;
		
		$result['count'] = $count;
	
		return $result;
	}

	//获取需要导入的列表
	function get_import_list($arr=array(),$page=1,$size=10){
		
		$result = array('list'=>array(),'count'=>0);
		
		
		
		$where = $this->get_wheres($arr);
		$where .= " AND pid = 0";
		if($arr['id']){
			$where .= " AND id != {$arr['id']}";
		}
		
		$sql = "select * from ".$this->db->dbprefix.$this->table." where {$where} ORDER BY id DESC limit ".($page-1)*$size.",{$size}";
		
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		$result['list'] = $list;
		
		
		$sql = "select count(*) as num from ".$this->db->dbprefix.$this->table." where {$where} ";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		$count = $row?$row['num']:0;
		
		$result['count'] = $count;
	
		return $result;
	}
	
	function get_all($arr=array()){
		
		$where = $this->get_wheres($arr);
		
		$sql = "select * from ".$this->db->dbprefix.$this->table." where {$where} ORDER BY id DESC limit 1000";
		
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		return $list;
	}
	
	function get_wheres($arr=array()){
		$where = " 1 ";
		if(isset($arr['date_month']) && !empty($arr['date_month'])){
			$where .= " AND date_month ='{$arr['date_month']}'";
		}
		
		if(isset($arr['region']) && !empty($arr['region'])){
			$where .= " AND region ='{$arr['region']}'";
		}
		// if(isset($arr['company']) && !empty($arr['company'])){
		// 	$where .= " AND company ='{$arr['company']}'";
		// }
		if(isset($arr['bumen']) && !empty($arr['bumen'])){
			$where .= " AND bumen like '%{$arr['bumen']}%'";
		}

		//如果存在is_export，表示是导出表格
		if(isset($arr['is_export'])){
			if($arr['is_export'] == 1){
				if(isset($arr['subject']) && !empty($arr['subject'])){
					$where .= " AND subject ='{$arr['subject']}' AND pid = 0";
				}
			}else{
				if(isset($arr['subject']) && !empty($arr['subject'])){
					$where .= " AND (subject ='{$arr['subject']}' OR pid > 0)";
				}
			}
		}else{
			if(isset($arr['subject']) && !empty($arr['subject'])){
				$where .= " AND subject ='{$arr['subject']}'";
			}
		}

		if(isset($arr['bz'])){
			if($arr['bz']=='人民币'){
				$where .= " AND (bz ='{$arr['bz']}' OR bz ='')";
			}elseif($arr['bz']=='all'){

			}
			else{
				$where .= " AND bz ='{$arr['bz']}'";
			}
		}
		if(isset($arr['keyword']) && !empty($arr['keyword'])){
			$where .= " AND info LIKE '%{$arr['keyword']}%'";
		}
		if(isset($arr['user_id']) && !empty($arr['user_id'])){
			$where .= " AND `user_id` ='{$arr['user_id']}'";
		}
		
		if(isset($arr['type'])){
			if(empty($arr['type']) || $arr['type'] != 'all'){
				$where .= " AND `type` ='{$arr['type']}'";
			}
		}
		
		if(isset($arr['mtype'])){
			$where .= " AND `mtype` ='{$arr['mtype']}'";
		}
		
		if(isset($arr['min_date']) && !empty($arr['min_date']) && isset($arr['max_date']) && !empty($arr['max_date'])){
			//$where .= " AND `date` >= '{$arr['min_date']}' AND `date` <= '{$arr['max_date']}'";
			$where .= " AND (date(date) between '{$arr['min_date']}' AND '{$arr['max_date']}')";
		}

		if(isset($arr['pid']) && !empty($arr['pid'])){
			$where .= " AND `pid` ='{$arr['pid']}'";
		}
		
		if(isset($arr['company_ids']) && !empty($arr['company_ids'])){
			$this->load->model('User/companyModel','companyModel');
			$res = $this->companyModel->ids_get_data($arr['company_ids']);
			$name = array();
			foreach($res as $val){
				$name[] = $val['name'];
				$sx_name[] = $val['sx_name'];
			}
			
			
			if(isset($arr['company']) && !empty($arr['company'])){
				if(in_array($arr['company'],$name) || in_array($arr['company'],$sx_name)){
					$where .= " AND company ='{$arr['company']}'";
				}
			}else{
				//$where .= " AND (`company` in('".implode("','",$name)."') OR `company` in('".implode("','",$sx_name)."'))";
				$where .= " AND `company` in('".implode("','",$sx_name)."')";
			}
		}else{
			if(isset($arr['company']) && !empty($arr['company'])){
				if(isset($arr['sx_name']) && !empty($arr['sx_name'])){
					$where .= " AND (company ='{$arr['company']}' OR company ='{$arr['sx_name']}')";
				}else{
					$where .= " AND company ='{$arr['company']}'";
				}
			}
		}

		//状态： 0全部(默认) 1已清(结余为0的数据) 2未清(结余不为0的数据)
		if(isset($arr['status']) && $arr['status'] > 0){
			if($arr['status'] == 1){
				$where .= " AND status = 1";
			}else{
				$where .= " AND status = 0";
			}
		}

		if(isset($arr['currentID']) && !empty($arr['currentID'])){
			$where .= " AND `id` < '{$arr['currentID']}' ";
		}

		//计算业务收入发生额
		if(isset($arr['in_subject']) && !empty($arr['in_subject'])){
			$where .= " AND `subject` in ({$arr['in_subject']})";
		}

		//确保不是已经删除的数据
		$where .= " AND is_del = 0";
		
		return $where;
	}
	
	function get_totals($arr=array(),$field='income'){
		$where = $this->get_wheres($arr);
		if($field==''){
			$field_str = "IF(mtype=0,income,expenses) as num";
		}
		else{
			
			$field_str = "IFNULL(SUM({$field}),0) as num";
		}
		$sql = "select {$field_str} from ".$this->db->dbprefix.$this->table." where {$where} limit 1";
		
		if(isset($this->totals[$sql])){
			return $this->totals[$sql];
		}
		
		$query = $this->db->query($sql);
		$row = $query->row_array();
		
		$num = $row?$row['num']:0;
		$this->totals[$sql] = $num;
		
		return $num;
	}
	
	function get_info($id){
		$sql  = "select * from ".$this->db->dbprefix.$this->table." where id='{$id}' limit 1";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		return $row;
	}
	
	function get_year_total($arr=array()){
		$year = date('Y',strtotime($arr['date_month'].'-01'));
		
		$where = " date_month LIKE '{$year}%' ";
		
		if(isset($arr['company']) && !empty($arr['company'])){
			$where .= " AND company ='{$arr['company']}'";
		}
		if(isset($arr['subject']) && !empty($arr['subject'])){
			$where .= " AND subject ='{$arr['subject']}'";
		}
		if(isset($arr['bz'])){
			if($arr['bz']=='人民币'){
				$where .= " AND (bz ='{$arr['bz']}' OR bz ='')";
			}
			else if($arr['bz']=='all'){}
			else{
				$where .= " AND bz ='{$arr['bz']}'";
			}
		}

		//把已经删除的去掉
		$where .= " AND is_del = 0";
		
		$sql  = "select ifnull(sum(money),0) as num from ".$this->db->dbprefix.$this->table." where {$where}";
		
		$query = $this->db->query($sql);
		$row = $query->row_array();
		
		return $row?$row['num']:0;
	}
	
	//已经还款的总数
	function get_huai_total($id){
		$sql  = "select ifnull(sum(income),0) as num from ".$this->db->dbprefix.$this->table." where pid='{$id}' AND mtype = 0 AND is_del = 0 limit 1";
		$query = $this->db->query($sql);
		$row = $query->row_array();
		
		return $row?$row['num']:0;
	}
	
	function get_huai_list($id){
		$sql  = "select * from ".$this->db->dbprefix.$this->table." where pid='{$id}' AND mtype = 0 AND is_del = 0 order by id desc";
		$query = $this->db->query($sql);
		$res = $query->result_array();
		
		return $res;
	}
	
	function get_all_id($arr=array()){
		$where = $this->get_wheres($arr);
		
		$sql = "select id from ".$this->db->dbprefix.$this->table." where {$where}  ";
		$query = $this->db->query($sql);
		$res = $query->result_array();
		$list = array();
		foreach($res as $val){
			$list[] = $val['id'];
		}
		return $list;
	}

	//导入需要的记账,ling添加
	function import_data($pid, $ids=array()){
		$data = array('pid' => $pid);
		$this->db->where_in('id', $ids);
		return $this->db->update($this->db->dbprefix.$this->table, $data);
	}

	//获取所选数据的收入总数,并返回最新还款的日期
	function get_sum_byId($ids=array()){
		$ids = implode(',', $ids);
		$sql  = "select ifnull(sum(income),0) as num from ".$this->db->dbprefix.$this->table." where id in ({$ids}) limit 1";
		
		$query = $this->db->query($sql);
		$row = $query->row_array();

		$sql2 = "select date from ".$this->db->dbprefix.$this->table." where id in ({$ids}) order by addtime desc limit 1";
		$query2 = $this->db->query($sql2);
		$row2 = $query2->row_array();

		$res = array('total'=>$row['num'],'maxtime'=>strtotime($row2['date']));
		return $res;
	}
}

